# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
"""
from itemadapter import ItemAdapter


class StockScrapyPipeline:
    def process_item(self, item, spider):
        return item
"""

import pymysql
from itemadapter import ItemAdapter


class StockScrapyPipeline:
    def __init__(self, host, user, password, db, port):
        self.host = host
        self.user = user
        self.password = password
        self.db = db
        self.port = port

    @classmethod
    def from_crawler(cls, crawler):
        return cls(
            host=crawler.settings.get("MYSQL_HOST"),
            user=crawler.settings.get("MYSQL_USER"),
            password=crawler.settings.get("MYSQL_PASSWORD"),
            db=crawler.settings.get("MYSQL_DB"),
            port=crawler.settings.get("MYSQL_PORT"),
        )

    def open_spider(self, spider):
        self.conn = pymysql.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            db=self.db,
            port=self.port,
        )
        self.cursor = self.conn.cursor()

    def process_item(self, item, spider):
        
        sql="INSERT INTO exsc_trade_info (date_time, could_buy, could_sell, report_buy_rate, report_sell_rate) SELECT %s, %s, %s, %s, %s WHERE NOT EXISTS (SELECT 1 FROM exsc_trade_info WHERE date_time = %s);"
        self.cursor.execute(
            sql,
            (
                item["date_time"],
                item["could_buy"],
                item["could_sell"],
                item["report_buy_rate"],
                item["report_sell_rate"],
                item["date_time"],
            ),
        )
        # Commit the transaction
        self.conn.commit()
        # Log the insertion
        spider.logger.info(
            "Inserted item: %s, could_buy: %s, could_sell: %s, report_buy_rate: %s, report_sell_rate: %s",
            (
                item["date_time"],
                item["could_buy"],
                item["could_sell"],
                item["report_buy_rate"],
                item["report_sell_rate"],
            ),
        )

        inquiry_settle_date_sql = "SELECT EXISTS(SELECT 1 FROM exsc_trade_info WHERE date_time = %s)"
        self.cursor.execute(inquiry_settle_date_sql, (item["settle_date_time"],))
        exists = self.cursor.fetchone()[0]
        if exists:
            self.cursor.execute(
                "UPDATE exsc_trade_info SET settle_buy_rate = %s, settle_sell_rate = %s WHERE date_time = %s",
                (
                    item["settle_buy_rate"],
                    item["settle_sell_rate"],
                    item["settle_date_time"],
                ),
            )
            self.conn.commit()
        else:
            self.cursor.execute(
                "INSERT INTO exsc_trade_info (date_time, settle_buy_rate, settle_sell_rate) VALUES (%s, %s, %s)",
                (
                    item["settle_date_time"],
                    item["settle_buy_rate"],
                    item["settle_sell_rate"],
                ),
            )
            spider.logger.debug(
                "Inserted settlement data for date: %s, settle_buy_rate: %s, settle_sell_rate: %s",
                (
                    item["settle_date_time"],
                    item["settle_buy_rate"],
                    item["settle_sell_rate"],
                ),
            )
            self.conn.commit()
        # Return the item for further processing
        return item

    def close_spider(self, spider):
        self.cursor.close()
        self.conn.close()
